package com.gjintel.business.report.dao.Impl;

import java.math.BigDecimal;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.gjintel.business.proj.model.DepositReturn;
import com.gjintel.business.proj.model.ProjectBidInfo;
import com.gjintel.business.proj.model.ProjectInfo;
import com.gjintel.business.report.dao.IReportDao;
import com.gjintel.framework.register.model.DepositCategory;
import com.gjintel.sdk.base.dao.HibernateGenericDAO;
import com.gjintel.sdk.base.dao.Page;
import com.gjintel.sdk.base.dao.Sort;
import com.gjintel.sdk.util.StringUtil;

@Repository("reportDao")
public class ReportDaoImpl  extends HibernateGenericDAO implements IReportDao{

	@Override
	public List<DepositCategory> list() {
		String sql ="select count(*) as count,t.name as name from `t_sys_register_deposit` d,`t_base_project_type` t WHERE d.deposit_categoryId=t.id GROUP BY d.deposit_categoryId ";
		List<DepositCategory> list=this.findBySQL(DepositCategory.class, sql);
		return list;
	}
	

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositReturn> getDepositList(String projectName, String code,Page page,Integer depositType, String projectType) {
		List<Object> params = new ArrayList<Object>();
	    StringBuffer hql= new StringBuffer("from DepositReturn dr where 1 = 1 ");
		if(projectName!= null && !projectName.equals("")){
			hql.append("and dr.projectName like ?" );
			params.add("%" +projectName+ "%");
		} if(code!= null && !code.equals("")){
			hql.append("and dr.projectCode like ?");
			params.add("%" +code+ "%");
		}if(depositType !=null){
			hql.append("and dr.pk.depositType= ?");
			params.add(depositType);
		}if(projectType !=null && !projectType.equals("") ){
			hql.append("and dr.projectType like ?");
			params.add("%" +projectType+ "%");
		}
		return this.findAsPage(hql.toString(), page, params.toArray());
	}

	@Override
	public List<ProjectInfo> findReportByProjectCase(ProjectInfo project, Page page, Sort sort, Date startTime,
			Date stopTime,Double startMoney,Double endMoney) {
		List<Object> paramList=new ArrayList<>();
		//StringBuffer hql=new StringBuffer("from ProjectInfo project where project.tradeTime!=null ");//查询结项、流标、撤销的项目
		StringBuffer sql=new StringBuffer();
		sql.append("select p.id as id,p.name as name,p.catagory_name as catagoryName,p.buy_name as buyName,p.budget_amount as budgetAmount,p.code as code "
			+" ,p.create_time as createTime,p.compet_release_time as competReleaseTime,p.hit_show_start_time as hitShowStartTime"
			+" ,p.trade_time as tradeTime,p.status as status "
			// 是否评标
			+" ,(case p.auditable "
			+"	when 0 then 'false'"
			+"	else 'true' "
			+"  end) as tendererName "	
			//中标单位
			+" ,(case p.auditable "
			+"	when 'false' then (select group_concat(`bidder_name`) from `t_proj_bid_info` where `proj_id`=p.`id` and `is_mult_hit`=true)"
			+"	else p.winnning_name "
			+"  end) as winningName "	
			//中标金额
			+" ,(case p.auditable "
			+"	when 'false' then (select group_concat(`bid_amount`) from `t_proj_bid_info` where `proj_id`=p.`id` and `is_mult_hit`=true)"
			+"	else p.hit_Money "
			+"  end) as money "
			+" from `t_proj_info` p where p.trade_time is not null ");
		
		//业务数据条件
		if(project!=null){
			if (!StringUtil.isEmpty(project.getName())) {//名称
				sql.append(" and p.name like ?");
				paramList.add("%"+project.getName()+"%");
			}
			if (!StringUtil.isEmpty(project.getCode())) {//编号
				sql.append(" and p.code like ?");
				paramList.add("%"+project.getCode()+"%");
			}
			if (!StringUtil.isEmpty(project.getCatagoryId())) {//类型
				sql.append(" and p.catagory_id = ?");
				paramList.add(project.getCatagoryId());
			}
			if (!StringUtil.isEmpty(project.getFundFrom())) {//资金性质
				sql.append(" and p.fund_from = ?");
				paramList.add(project.getFundFrom());
			}
		}
		
		//交易时间段
		if(startTime != null){
			sql.append(" and p.trade_time >= ? ");
			paramList.add(startTime);
		}
		if(stopTime != null){
			sql.append(" and p.trade_time <= ? ");
			paramList.add(stopTime);
		}
		//项目预算
		if(startMoney != null){
			sql.append(" and p.budget_amount >= ? ");
			paramList.add(startMoney);
		}
		if(endMoney != null){
			sql.append(" and p.budget_amount <= ? ");
			paramList.add(endMoney);
		}
		
		sql.append(" order by p.create_time desc");
		return this.findPageByComSql(ProjectInfo.class, sql.toString(), page, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositReturn> getDepositListExport(String projectName, String code,Integer depositType, String projectType) {
		List<Object> params = new ArrayList<Object>();
	    StringBuffer hql= new StringBuffer("from DepositReturn dr where 1 = 1 ");
		if(projectName!= null && !projectName.equals("")){
			hql.append("and dr.projectName like ?");
			params.add("%" +projectName+ "%");
		} if(code!= null && !code.equals("")){
			hql.append("and dr.projectCode like ?");
			params.add("%" +code+ "%");
		} if(depositType !=null){
			hql.append("and dr.pk.depositType= ?");
			params.add(depositType);
		}if(projectType !=null && !projectType.equals("")){
			hql.append("and dr.projectType like ?");
			params.add("%" +projectType+ "%");
		}
		return this.find(hql.toString(), params.toArray());
	}

	@Override
	public BigDecimal findDepositAmountByDate(Integer depositType,Integer status, Date startTime, Date stopTime,String className) {
		List<Object> params=new ArrayList<>();
		StringBuffer hql=new StringBuffer("select SUM(d.tnAmount) from "+className+" d where d.pk.depositType=? ");
		params.add(depositType);
		
		//时间段(收入)--入账时间
		if(startTime != null&&status==null){
			hql.append(" and d.transTime >= ? ");
			params.add(startTime);
		}
		if(stopTime != null&&status==null){
			hql.append(" and d.transTime <= ? ");
			params.add(stopTime);
		}
		
		//时间段(支出)--退款时间
		if(startTime != null&&status!=null){
			hql.append(" and d.returnTime >= ? ");
			params.add(startTime);
		}
		if(stopTime != null&&status!=null){
			hql.append(" and d.returnTime <= ? ");
			params.add(stopTime);
		}
				
		
		if(status!=null){
			hql.append(" and d.status=?");
			params.add(status);
		}
		return (BigDecimal) this.findUnique(hql.toString(), params.toArray());
	}


	@Override
	public List findDepositListByDate(Page page,Integer depositType, Integer status, Date startTime, Date stopTime,String className) {
		List<Object> params=new ArrayList<>();
		StringBuffer hql=new StringBuffer("from "+className+" d where 1=1 ");
		//时间段(收入)--入账时间
		if(startTime != null&&status==null){
			hql.append(" and d.transTime >= ? ");
			params.add(startTime);
		}
		if(stopTime != null&&status==null){
			hql.append(" and d.transTime <= ? ");
			params.add(stopTime);
		}
		
		//时间段(支出)--退款时间
		if(startTime != null&&status!=null){
			hql.append(" and d.returnTime >= ? ");
			params.add(startTime);
		}
		if(stopTime != null&&status!=null){
			hql.append(" and d.returnTime <= ? ");
			params.add(stopTime);
		}
		if(status!=null){
			hql.append(" and d.status=?");
			params.add(status);
		}
		if(depositType!=null){//保证金类型
			hql.append(" and d.pk.depositType=?");
			params.add(depositType);
		}
		return this.findAsPage(hql.toString(), page, params.toArray());
	}


	@Override
	public List findReportByProjectList(ProjectInfo project, Page page, Sort sort, Date startTime, Date stopTime) {
		List<Object> paramList=new ArrayList<>();
		StringBuffer hql=new StringBuffer("from ProjectInfo project where project.bidStartTime!=null ");//查询有开标时间的
		
		//开标时间段
		if(startTime != null){
			hql.append(" and project.bidStartTime >= ? ");
			paramList.add(startTime);
		}
		if(stopTime != null){
			hql.append(" and project.bidStartTime <= ? ");
			paramList.add(stopTime);
		}
		
		hql.append(" order by project.bidStartTime desc");
		return this.findAsPage(hql.toString(), page, sort, "project", false, paramList.toArray());
	}
	
	@Override
	public List findReportByNoOpentBidProjectList(ProjectInfo project, Page page, Sort sort, Date startTime, Date stopTime) {
		List<Object> paramList=new ArrayList<>();
		StringBuffer hql=new StringBuffer("from ProjectInfo project where project.bidStartTime!=null and project.status in (7) ");//查询有开标时间的,并且未开标
		
		//开标时间段
		if(startTime != null){
			hql.append(" and project.bidStartTime >= ? ");
			paramList.add(startTime);
		}
		if(stopTime != null){
			hql.append(" and project.bidStartTime <= ? ");
			paramList.add(stopTime);
		}
		if(project != null){
			hql.append(" and project.auditable = ? ");
			paramList.add(project.getAuditable());
		}

		hql.append(" order by project.bidStartTime desc");
		return this.findAsPage(hql.toString(), page, sort, "project", false, paramList.toArray());
	}


}
